package com.hqyj.bigdata_project.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hqyj.bigdata_project.entity.TopTenCatalogInfo;
import com.hqyj.bigdata_project.entity.TopTenVideoInfo;
import com.hqyj.bigdata_project.mapper.TopTenCatalogInfoMapper;
import com.hqyj.bigdata_project.service.TopTenCatalogInfoService;
import com.hqyj.bigdata_project.util.HiveUtil;
import org.apache.hadoop.hive.metastore.ObjectStore;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.transaction.Transactional;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Service
public class TopTenCatalogInfoServiceImpl extends ServiceImpl<TopTenCatalogInfoMapper, TopTenCatalogInfo> implements TopTenCatalogInfoService {

    private TopTenCatalogInfoMapper topTenCatalogInfoMapper;

    @Transactional(rollbackOn = Exception.class)
    @Override
    public boolean initTopTenCatalogInfo() throws Exception {
        String hql = "select\n" +
                "   category_name,\n" +
                "   count(*) category_count\n" +
                "from\n" +
                "   (select\n" +
                "   videoId,\n" +
                "   category_name\n" +
                "from\n" +
                "   gulivideo_orc\n" +
                "lateral view explode(category) tmp_category as category_name)t1\n" +
                "group by \n" +
                "   category_name\n" +
                "order by\n" +
                "   category_count desc\n" +
                "limit 10";
        ResultSet rs = null;
        List<TopTenCatalogInfo> topTenCatalogInfoList = new ArrayList<TopTenCatalogInfo>();
        try {
            rs = HiveUtil.query(hql);
            while (rs.next()) {
                TopTenCatalogInfo topTenCatalogInfo = TopTenCatalogInfo.builder().categoryName(rs.getString("category_name")).categoryCount(rs.getLong("category_count")).type(10).build();
                topTenCatalogInfoList.add(topTenCatalogInfo);
            }
            return super.saveBatch(topTenCatalogInfoList);
        } catch (Exception ex) {
            ex.printStackTrace();
            //手动回滚
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
    }

    @Override
    public List<TopTenCatalogInfo> queryTopTenCatalogInfoList() {
        QueryWrapper<TopTenCatalogInfo> queryWrapper = new QueryWrapper<TopTenCatalogInfo>();
        queryWrapper.eq("type",10);
        return super.list(queryWrapper);
    }

    @Transactional(rollbackOn = Exception.class)
    @Override
    public boolean initTopTwentyCatalogInfo() throws Exception {
        String hql = "select\n" +
                "   category_name,\n" +
                "   count(*) category_count\n" +
                "from\n" +
                "   (select\n" +
                "   explode(category) category_name\n" +
                "from\n" +
                "   (select\n" +
                "   category\n" +
                "from\n" +
                "   (select\n" +
                "   related_id\n" +
                "from\n" +
                "   (select\n" +
                "   relatedId,\n" +
                "   views\n" +
                "from\n" +
                "   gulivideo_orc\n" +
                "order by \n" +
                "   views desc\n" +
                "limit 50)t1\n" +
                "lateral view explode(relatedId) tmp_relatedId as related_id\n" +
                "group by \n" +
                "   related_id)t2\n" +
                "join gulivideo_orc orc\n" +
                "on t2.related_id=orc.videoId)t3)t4\n" +
                "group by \n" +
                "   category_name\n" +
                "order by\n" +
                "   category_count desc";
        ResultSet rs = null;
        List<TopTenCatalogInfo> topTenCatalogInfoList = new ArrayList<TopTenCatalogInfo>();
        try {
            rs = HiveUtil.query(hql);
            while (rs.next()) {
                TopTenCatalogInfo topTenCatalogInfo = TopTenCatalogInfo.builder().categoryName(rs.getString("category_name")).categoryCount(rs.getLong("category_count")).type(20).build();
                topTenCatalogInfoList.add(topTenCatalogInfo);
            }
            return super.saveBatch(topTenCatalogInfoList);
        } catch (Exception ex) {
            ex.printStackTrace();
            //手动回滚
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return false;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
    }

    @Override
    public List<TopTenCatalogInfo> queryTopTwentyCatalogInfoList() {
        QueryWrapper<TopTenCatalogInfo> queryWrapper = new QueryWrapper<TopTenCatalogInfo>();
        queryWrapper.eq("type",20);
        return super.list(queryWrapper);
    }


}
